import pandas as pd

# 读取所有学生表格，进行合并
df = pd.read_excel(r'C:\Users\liutong\Documents\Tencent Files\707921012\FileRecv\16学生.xlsx')
df1 = pd.read_excel(r'C:\Users\liutong\Documents\Tencent Files\707921012\FileRecv\17学生.xlsx')
df2 = pd.read_excel(r'C:\Users\liutong\Documents\Tencent Files\707921012\FileRecv\18学生.xlsx')
frames = [df1, df2, df]
df3 = df.append([df1, df2], ignore_index=True)  # 整合为一个表格

df3.to_excel(r'C:\Users\liutong\Documents\Tencent Files\707921012\FileRecv\整合学生.xlsx')  # 保存文件
# cols = [i for i in df.columns if i not in ['性别', '专业', '序号', 'Unnamed: 36', '测验工具', '测验用时', '院系']]
# df3 = df3[cols].fillna(df3.mean())
# cols = ['神经质(N)标准分', '掩饰性(L)标准分', '内外向(E)标准分', '强迫症状', '人际关系敏感', '抑郁', '焦虑', '敌对', '恐怖', '偏执', '精神病性', '其他']
# for n in cols:
#     print(df3[n].sum())
